package JaggSoft;

import JaggSoft.Utilidades.*;
import java.awt.*;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.*;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.table.TableRowSorter;
import net.sourceforge.jdatepicker.impl.JDatePickerImpl;

public class ReporteDevoluciones extends javax.swing.JInternalFrame {
    private JDatePickerImpl sel_soli_fecha_inicial,sel_soli_fecha_final,sel_dev_fecha_inicial,sel_dev_fecha_final;
    private DefaultListModel<ModeloItemValor> modelo = new DefaultListModel<ModeloItemValor>();
    private JCheckBox jchkSolicitudFechaInicio,jchkSolicitudFechaFinal,jchkDevolucionFechaInicio,jchkDevolucionFechaFinal;
    private JFileChooser selector;
    public ReporteDevoluciones() {
        initComponents();
        this.setLocation(new Point(70, 0));
        selector=new JFileChooser();
        selector.setFileFilter(new FileNameExtensionFilter("Libro de Excel",new String[]{"xls","xlsx"}));
        selector.setMultiSelectionEnabled(false);
        selector.setSelectedFile(new File("reporte_devoluciones.xls"));
        jlstSolicitantes.setModel(modelo);
        jchkSolicitudFechaInicio=new JCheckBox("");
        jchkSolicitudFechaFinal=new JCheckBox("");
        jchkDevolucionFechaInicio=new JCheckBox("");
        jchkDevolucionFechaFinal=new JCheckBox("");
        jchkSolicitudFechaInicio.setToolTipText("Incluir");
        jchkSolicitudFechaFinal.setToolTipText("Incluir");
        jchkDevolucionFechaInicio.setToolTipText("Incluir");
        jchkDevolucionFechaFinal.setToolTipText("Incluir");
        sel_soli_fecha_inicial = Util.ColocarSelectorFecha(jpSolicitudFechaInicial, null,new Component[]{jchkSolicitudFechaInicio});
        sel_soli_fecha_final = Util.ColocarSelectorFecha(jpSolicitudFechaFinal, null,new Component[]{jchkSolicitudFechaFinal});
        sel_dev_fecha_inicial = Util.ColocarSelectorFecha(jpDevolucionFechaInicial, null,new Component[]{jchkDevolucionFechaInicio});
        sel_dev_fecha_final = Util.ColocarSelectorFecha(jpDevolucionFechaFinal, null,new Component[]{jchkDevolucionFechaFinal});
        CargarAreas();
        jcbArea.setSelectedIndex(-1);
        CargarDatos("");
        //JOptionPane.showMessageDialog(null,"Agregar en la tabla de detalle la cantidad que se solicito y la que se ha devuelto y lo pendiente.\nVer si necesita algun filtro extra o no y hacer las modificaciones necesarias.");
    }
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jtfSolicitud = new javax.swing.JTextField();
        jLabel2 = new javax.swing.JLabel();
        jcbArea = new javax.swing.JComboBox();
        jPanel1 = new javax.swing.JPanel();
        jLabel4 = new javax.swing.JLabel();
        jpSolicitudFechaInicial = new javax.swing.JPanel();
        jLabel5 = new javax.swing.JLabel();
        jpSolicitudFechaFinal = new javax.swing.JPanel();
        jPanel2 = new javax.swing.JPanel();
        jLabel6 = new javax.swing.JLabel();
        jpDevolucionFechaInicial = new javax.swing.JPanel();
        jLabel7 = new javax.swing.JLabel();
        jpDevolucionFechaFinal = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jtblDetalleDevolucion = new javax.swing.JTable();
        jScrollPane3 = new javax.swing.JScrollPane();
        jtblDevoluciones = new javax.swing.JTable();
        jPanel3 = new javax.swing.JPanel();
        jtfSolicitante = new javax.swing.JTextField();
        jLabel3 = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jlstSolicitantes = new javax.swing.JList();
        jbtnExportar = new javax.swing.JButton();
        jbtnBuscar = new javax.swing.JButton();

        setClosable(true);
        setTitle("Reporte de Devoluciones");
        setFrameIcon(new javax.swing.ImageIcon(getClass().getResource("/Recursos/reporte_devoluciones.png"))); // NOI18N
        addComponentListener(new java.awt.event.ComponentAdapter() {
            public void componentMoved(java.awt.event.ComponentEvent evt) {
                formComponentMoved(evt);
            }
            public void componentResized(java.awt.event.ComponentEvent evt) {
                formComponentResized(evt);
            }
        });

        jLabel1.setText("N° Solicitud:");

        jtfSolicitud.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyTyped(java.awt.event.KeyEvent evt) {
                jtfSolicitudKeyTyped(evt);
            }
        });

        jLabel2.setText("Área/Departamento:");

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Solicitud"));

        jLabel4.setText("Fecha Inicial:");

        javax.swing.GroupLayout jpSolicitudFechaInicialLayout = new javax.swing.GroupLayout(jpSolicitudFechaInicial);
        jpSolicitudFechaInicial.setLayout(jpSolicitudFechaInicialLayout);
        jpSolicitudFechaInicialLayout.setHorizontalGroup(
            jpSolicitudFechaInicialLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 144, Short.MAX_VALUE)
        );
        jpSolicitudFechaInicialLayout.setVerticalGroup(
            jpSolicitudFechaInicialLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 20, Short.MAX_VALUE)
        );

        jLabel5.setText("Fecha Final:");

        javax.swing.GroupLayout jpSolicitudFechaFinalLayout = new javax.swing.GroupLayout(jpSolicitudFechaFinal);
        jpSolicitudFechaFinal.setLayout(jpSolicitudFechaFinalLayout);
        jpSolicitudFechaFinalLayout.setHorizontalGroup(
            jpSolicitudFechaFinalLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 144, Short.MAX_VALUE)
        );
        jpSolicitudFechaFinalLayout.setVerticalGroup(
            jpSolicitudFechaFinalLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 20, Short.MAX_VALUE)
        );

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel4)
                    .addComponent(jpSolicitudFechaInicial, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel5)
                    .addComponent(jpSolicitudFechaFinal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(jLabel4)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jpSolicitudFechaInicial, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabel5)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jpSolicitudFechaFinal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
        );

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Devolución"));

        jLabel6.setText("Fecha Inicial:");

        javax.swing.GroupLayout jpDevolucionFechaInicialLayout = new javax.swing.GroupLayout(jpDevolucionFechaInicial);
        jpDevolucionFechaInicial.setLayout(jpDevolucionFechaInicialLayout);
        jpDevolucionFechaInicialLayout.setHorizontalGroup(
            jpDevolucionFechaInicialLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 144, Short.MAX_VALUE)
        );
        jpDevolucionFechaInicialLayout.setVerticalGroup(
            jpDevolucionFechaInicialLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 20, Short.MAX_VALUE)
        );

        jLabel7.setText("Fecha Final:");

        javax.swing.GroupLayout jpDevolucionFechaFinalLayout = new javax.swing.GroupLayout(jpDevolucionFechaFinal);
        jpDevolucionFechaFinal.setLayout(jpDevolucionFechaFinalLayout);
        jpDevolucionFechaFinalLayout.setHorizontalGroup(
            jpDevolucionFechaFinalLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 144, Short.MAX_VALUE)
        );
        jpDevolucionFechaFinalLayout.setVerticalGroup(
            jpDevolucionFechaFinalLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 20, Short.MAX_VALUE)
        );

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel6)
                    .addComponent(jpDevolucionFechaInicial, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel7)
                    .addComponent(jpDevolucionFechaFinal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(jLabel6)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jpDevolucionFechaInicial, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabel7)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jpDevolucionFechaFinal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
        );

        jScrollPane2.setViewportView(jtblDetalleDevolucion);

        jtblDevoluciones.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jtblDevolucionesMouseClicked(evt);
            }
        });
        jScrollPane3.setViewportView(jtblDevoluciones);

        jtfSolicitante.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyTyped(java.awt.event.KeyEvent evt) {
                jtfSolicitanteKeyTyped(evt);
            }
        });

        jLabel3.setText("Solicitante:");

        jScrollPane1.setViewportView(jlstSolicitantes);

        jbtnExportar.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Recursos/excel.png"))); // NOI18N
        jbtnExportar.setToolTipText("Exportar a Excel");
        jbtnExportar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jbtnExportarActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel3Layout.createSequentialGroup()
                        .addComponent(jLabel3)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jtfSolicitante, javax.swing.GroupLayout.PREFERRED_SIZE, 150, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(0, 0, Short.MAX_VALUE))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel3Layout.createSequentialGroup()
                        .addGap(0, 0, Short.MAX_VALUE)
                        .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, 213, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jbtnExportar, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE))))
                .addContainerGap())
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE, false)
                    .addComponent(jLabel3)
                    .addComponent(jtfSolicitante, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 99, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jbtnExportar, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );

        jbtnBuscar.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Recursos/accion_buscar_mini.png"))); // NOI18N
        jbtnBuscar.setToolTipText("Buscar");
        jbtnBuscar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jbtnBuscarActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                    .addComponent(jScrollPane2)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel1)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(jtfSolicitud, javax.swing.GroupLayout.PREFERRED_SIZE, 70, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(jbtnBuscar, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(jLabel2)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(jcbArea, javax.swing.GroupLayout.PREFERRED_SIZE, 144, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(100, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(jLabel1)
                                .addComponent(jtfSolicitud, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addComponent(jbtnBuscar, javax.swing.GroupLayout.PREFERRED_SIZE, 20, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE, false)
                                .addComponent(jLabel2)
                                .addComponent(jcbArea, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
                    .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 180, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 180, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(20, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents
    private void CargarDatos(String filtro){
        String consulta="SELECT d.id_devolucion,d.id_solicitud,DATE_FORMAT(s.fecha,'%d/%m/%Y') AS fecha_solicitud,"
                + "DATE_FORMAT(d.fecha,'%d/%m/%Y') AS fecha_devolucion,a.nombre AS `area`,CONCAT_WS(' ',so.nombres,so.apellidos) AS solicitante"
                + " FROM devolucion d INNER JOIN solicitud s ON d.id_solicitud=s.id_solicitud INNER JOIN `area` a ON s.id_area=a.id_area "
                + " INNER JOIN solicitante so ON s.id_solicitante=so.id_solicitante"+filtro+";";
        Util.CargarTabla(jtblDevoluciones,consulta,new String[]{"N° Devolución","N° Solicitud","Fecha Solicitud","Fecha Devolución","Área","Solicitante"});
        Util.AlinearColumnas(jtblDevoluciones,SwingConstants.CENTER);
        jtblDevoluciones.setRowSorter(new TableRowSorter<MyModeloTabla>((MyModeloTabla)jtblDevoluciones.getModel()));
    }
    private void CargarDatosDetalle(String id){
        String consulta="SELECT CONCAT_WS(' ',p.nombre,i.unidad_medida,i.detalle) AS producto,b.nombre AS bodega,IFNULL((SELECT SUM(ds.cantidad) "
                +"FROM detallesolicitud ds WHERE id_solicitud=(SELECT id_solicitud FROM devolucion WHERE id_devolucion=dd.id_devolucion) "
                +"AND ds.id_inventario=dd.id_inventario),0) AS prestado,dd.cantidad FROM detalledevolucion dd INNER JOIN inventario i "
                +"ON dd.id_inventario=i.id_inventario INNER JOIN producto p ON i.id_producto=p.id_producto INNER JOIN bodega b "
                +"ON dd.id_bodega=b.id_bodega WHERE dd.id_devolucion="+id+";";
        Util.CargarTabla(jtblDetalleDevolucion,consulta,new String[]{"Producto","Bodega","Prestado","Devuelto"});
        Util.AlinearColumnas(jtblDetalleDevolucion,SwingConstants.CENTER);
        jtblDetalleDevolucion.setRowSorter(new TableRowSorter<MyModeloTabla>((MyModeloTabla)jtblDetalleDevolucion.getModel()));
    }
    private void ConsultaFiltrada(){
        String filtro="";
        filtro=(jtfSolicitud.getText().length()>0?" WHERE s.id_solicitud="+jtfSolicitud.getText():"");
        filtro+=(jcbArea.getSelectedIndex()>-1?(!filtro.equals("")?" AND":" WHERE")+" a.id_area="+((ModeloItemValor)jcbArea.getSelectedItem()).getValor():"");
        filtro+=(jlstSolicitantes.getSelectedIndex()>-1?(!filtro.equals("")?" AND":" WHERE")+" so.id_solicitante="+((ModeloItemValor)jlstSolicitantes.getSelectedValue()).getValor():"");
        if(jchkSolicitudFechaInicio.isSelected()||jchkSolicitudFechaFinal.isSelected()||jchkDevolucionFechaInicio.isSelected()||jchkDevolucionFechaFinal.isSelected()){
            SimpleDateFormat formato = new SimpleDateFormat("yyyy/MM/dd");
            if(jchkSolicitudFechaInicio.isSelected()){
                String fecha_inicial=formato.format((Date)sel_soli_fecha_inicial.getModel().getValue());
                filtro+=(!filtro.equals("")?" AND":" WHERE")+" s.fecha >= '"+fecha_inicial+"'";
            }
            if(jchkSolicitudFechaFinal.isSelected()){
                String fecha_final=formato.format((Date)sel_soli_fecha_final.getModel().getValue());
                filtro+=(!filtro.equals("")?" AND":" WHERE")+" s.fecha <= '"+fecha_final+"'";
            }
            if(jchkDevolucionFechaInicio.isSelected()){
                String fecha_inicial=formato.format((Date)sel_dev_fecha_inicial.getModel().getValue());
                filtro+=(!filtro.equals("")?" AND":" WHERE")+" d.fecha >= '"+fecha_inicial+"'";
            }
            if(jchkDevolucionFechaFinal.isSelected()){
                String fecha_final=formato.format((Date)sel_dev_fecha_final.getModel().getValue());
                filtro+=(!filtro.equals("")?" AND":" WHERE")+" d.fecha <= '"+fecha_final+"'";
            }
        }
        CargarDatos(filtro);
    }
    private void CargarAreas(){
        jcbArea.removeAllItems();
        Util.CargarJComboBox(jcbArea,"SELECT id_area,nombre FROM `area`",new String[]{"Id","Area"});
    }
    private void formComponentMoved(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_formComponentMoved
        if(evt.getComponent().getLocation().x<70)
            evt.getComponent().setLocation(70,evt.getComponent().getLocation().y);
        if(evt.getComponent().getLocation().y<0)
            evt.getComponent().setLocation(evt.getComponent().getLocation().x,0);
    }//GEN-LAST:event_formComponentMoved
    private void formComponentResized(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_formComponentResized
        if(this.getParent().getWidth()-70<this.getWidth()  && this.isMaximum)
            this.setSize(this.getWidth()-70,this.getHeight());
    }//GEN-LAST:event_formComponentResized
    private void jtfSolicitanteKeyTyped(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_jtfSolicitanteKeyTyped
        modelo.removeAllElements();
        JTable tabla=Conexion.ObtenerTabla("SELECT id_solicitante,CONCAT_WS(' ',nombres,apellidos) FROM solicitante WHERE nombres LIKE '"+Util.ComponerTexto(jtfSolicitante.getText()+evt.getKeyChar())+"%' OR apellidos LIKE '"+Util.ComponerTexto(jtfSolicitante.getText()+evt.getKeyChar())+"%'",new String[]{"id","solicitante"});
        for (int i = 0; i < tabla.getRowCount(); i++)
        modelo.addElement(new ModeloItemValor(tabla.getValueAt(i, 1), tabla.getValueAt(i, 0)));
    }//GEN-LAST:event_jtfSolicitanteKeyTyped
    private void jtfSolicitudKeyTyped(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_jtfSolicitudKeyTyped
        if(!Util.esNumero(jtfSolicitud.getText()+evt.getKeyChar(),false))
            evt.consume();
    }//GEN-LAST:event_jtfSolicitudKeyTyped
    private void jbtnBuscarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jbtnBuscarActionPerformed
        ConsultaFiltrada();
    }//GEN-LAST:event_jbtnBuscarActionPerformed
    private void jtblDevolucionesMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jtblDevolucionesMouseClicked
        if(evt.getClickCount()==2 && !evt.isConsumed())
            CargarDatosDetalle(jtblDevoluciones.getValueAt(jtblDevoluciones.getSelectedRow(),0).toString());
    }//GEN-LAST:event_jtblDevolucionesMouseClicked

    private void jbtnExportarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jbtnExportarActionPerformed
        if(jtblDevoluciones.getRowCount()>0){
            if(selector.showSaveDialog(null)==JOptionPane.OK_OPTION){
                try {
                    String ruta=selector.getSelectedFile().getAbsolutePath();
                    if(!ruta.endsWith(".xls"))
                        ruta+=".xls";
                    File archivo_excel=new File(ruta);
                    switch(Util.ExportarReporteExcel(jtblDevoluciones,archivo_excel,"Reporte Devoluciones",
                            "SELECT CONCAT_WS(' ',p.nombre,i.unidad_medida,i.detalle) AS producto,b.nombre AS bodega,IFNULL((SELECT SUM(ds.cantidad) "
                            +"FROM detallesolicitud ds WHERE id_solicitud=(SELECT id_solicitud FROM devolucion WHERE id_devolucion=dd.id_devolucion) "
                            +"AND ds.id_inventario=dd.id_inventario),0) AS prestado,dd.cantidad FROM detalledevolucion dd INNER JOIN inventario i "
                            +"ON dd.id_inventario=i.id_inventario INNER JOIN producto p ON i.id_producto=p.id_producto INNER JOIN bodega b "
                            +"ON dd.id_bodega=b.id_bodega WHERE dd.id_devolucion=@id;",new String[]{"Producto","Bodega","Prestado","Devuelto"})){
                                case OK:
                                JOptionPane.showMessageDialog(null,"Archivo Exportado",this.getTitle(),JOptionPane.INFORMATION_MESSAGE);
                                Desktop.getDesktop().open(archivo_excel);
                                break;
                                case CANCELADO:
                                break;
                                case ERROR:
                                JOptionPane.showMessageDialog(null,"No se pudo exportar el archivo","Error",JOptionPane.ERROR_MESSAGE);
                                break;
                    }
                } catch (Exception ex) {System.err.println(ex.getMessage());}
            }
        }
        else
        JOptionPane.showMessageDialog(null,"No hay datos para exportar","Sin Datos",JOptionPane.WARNING_MESSAGE);
    }//GEN-LAST:event_jbtnExportarActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JButton jbtnBuscar;
    private javax.swing.JButton jbtnExportar;
    private javax.swing.JComboBox jcbArea;
    private javax.swing.JList jlstSolicitantes;
    private javax.swing.JPanel jpDevolucionFechaFinal;
    private javax.swing.JPanel jpDevolucionFechaInicial;
    private javax.swing.JPanel jpSolicitudFechaFinal;
    private javax.swing.JPanel jpSolicitudFechaInicial;
    private javax.swing.JTable jtblDetalleDevolucion;
    private javax.swing.JTable jtblDevoluciones;
    private javax.swing.JTextField jtfSolicitante;
    private javax.swing.JTextField jtfSolicitud;
    // End of variables declaration//GEN-END:variables
}